﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Spring.Data.Generic;
using Spring.Data.Common;
using Spring.Data.Objects.Generic;

namespace MySearch
{
    public class Search : KpmgObject
    {
        public string SearchID { get; set; }
        public string Name { get; set; }
        public bool IsPredefined { get; set; }
        public string OwnerStaffNo { get; set; }
        public DateTime CreatedTime { get; set; }
        public string CreatedUser { get; set; }

        public IList<SearchCondition> Conditions { get; private set; }

        public Search()
        {
            this.Conditions = new List<SearchCondition>();
        }
    }

    public enum SearchKey { Ticket_No, Status, Requestor_Staff_No, Assigned_Staff_No, Created_Time }

    public enum SearchOperator { Equal, NotEqual, Great, GreatOrEqual, Less, LessOrEqual, In }

    public enum SearchDataType { String, Date }

    public class SearchCondition
    {
        public SearchKey Key { get; set; }
        public SearchOperator Operator { get; set; }
        public string Value { get; set; }
        public SearchDataType DataType { get; set; }
        //public Dictionary<SearchOperator, string> DicOperators;
        private Dictionary<SearchOperator, string> _DicOperator;
        public Dictionary<SearchOperator, string> DicOperator
        {

            get
            {
                if (_DicOperator == null)
                {
                    Dictionary<SearchOperator, string> DicOperator = new Dictionary<SearchOperator, string>();
                    DicOperator.Add(SearchOperator.Equal, "=");
                    DicOperator.Add(SearchOperator.Great, ">");
                    DicOperator.Add(SearchOperator.GreatOrEqual, ">=");
                    DicOperator.Add(SearchOperator.Less, "<");
                    DicOperator.Add(SearchOperator.LessOrEqual, "<=");
                    DicOperator.Add(SearchOperator.In, "in");
                    DicOperator.Add(SearchOperator.NotEqual, "!=");

                    _DicOperator = DicOperator;

                }
                return _DicOperator;
            }

        }

        public SearchCondition(SearchKey key, SearchOperator oper, string val, SearchDataType dataType)
        {
            this.Key = key;
            this.Operator = oper;
            this.Value = val;
            this.DataType = dataType;

        }

    }
    public class MyTicket
    {
        public string Chargeable_Cost_Centre_No { set; get; }
        public string Requestor_Staff_No { set; get; }
        public string Requestor_Cost_Centre_No { set; get; }
        public string Assigned_Team_Code { set; get; }

    }
    internal static class SearchDAO
    {
        private static AdoTemplate template = AppConfigurer.AdoTemplate;

        private static string strBase = " select search_id, search_name, is_predefined, owner_staff_no, created_time, created_user "
            + " From search ";

        internal static Search GetSearch(string searchID)
        {
            string strSql = "select * from search";
            IDbParameters p = template.CreateDbParameters();

            IList<Search> lst = template.QueryWithRowMapperDelegate(CommandType.Text, strSql,
                new RowMapperDelegate<Search>(RowMap), p);
            FillConditions(lst);

            return lst.Count == 0 ? null : lst[0];
        }

        //
        internal static IList<MyTicket> SearchTicket(IList<SearchCondition> lst)
        {

            string strSql = "select Chargeable_Cost_Centre_No,Requestor_Staff_No,Requestor_Cost_Centre_No,Assigned_Team_Code from ticket where 1=1 ";
            IDbParameters p = template.CreateDbParameters();
            // p.Add("tNo", DbType.String).Value = TicketNo;
            bool isComed = false;
            foreach (SearchCondition sc in lst)
            {
                switch (sc.Key)
                {
                    case SearchKey.Created_Time:

                        if (!isComed)
                        {
                            if (sc.DicOperator.ContainsKey(sc.Operator))
                                strSql += " and DateDiff(d,@Created_Time,Created_Time)" + string.Format("{0}", sc.DicOperator[sc.Operator]) + "0";
                            p.Add("Created_Time", DbType.DateTime).Value = sc.Value;
                            isComed = true;
                        }
                        else
                        {
                            if (sc.DicOperator.ContainsKey(sc.Operator))
                                strSql += " and DateDiff(d,@Created_Time1,Created_Time)" + string.Format("{0}", sc.DicOperator[sc.Operator]) + "0";
                            p.Add("Created_Time1", DbType.DateTime).Value = sc.Value;

                        }
                        break;
                    case SearchKey.Requestor_Staff_No:
                        strSql += " and " + sc.Key + string.Format(" {0}", sc.DicOperator[sc.Operator]) + " @" + sc.Key;
                        p.Add(sc.Key.ToString(), DbType.String).Value = ChangeDisplayNameToStaffNo(sc.Value);
                        break;
                    case SearchKey.Assigned_Staff_No:
                        strSql += " and " + sc.Key + string.Format(" {0}", sc.DicOperator[sc.Operator]) + " @" + sc.Key;
                        p.Add(sc.Key.ToString(), DbType.String).Value = ChangeDisplayNameToStaffNo(sc.Value);
                        break;

                    default:
                        strSql += " and " + sc.Key + string.Format(" {0}", sc.DicOperator[sc.Operator]) + " @" + sc.Key;
                        p.Add(sc.Key.ToString(), DbType.String).Value = sc.Value;
                        break;
                }
            
            }

            IList<MyTicket> list = new List<MyTicket>();
            MyTicket t;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader idr)
            {
                t = new MyTicket
                {
                    Chargeable_Cost_Centre_No = idr.GetString(0),
                    Requestor_Staff_No = idr.GetString(1),
                    Requestor_Cost_Centre_No = idr.GetString(2),
                    Assigned_Team_Code = idr.GetString(3)
                };
                list.Add(t);

            }, p);
            return list;
        }
        #region Create search conditions
        internal static void CreateSearchCondition(Search s)
        {
            string strSql = "insert into Search values(@S_ID,@S_Name,@Is_Predefined,@Owner_Staff_No,@Create_Time,@Create_User) ";
            IDbParameters p = template.CreateDbParameters();
            s.SearchID = UniqueIDGenerator.Get("S");
            p.Add("S_ID", DbType.String).Value = s.SearchID;
            p.Add("S_Name", DbType.String).Value = s.Name;
            p.Add("Is_Predefined", DbType.String).Value = KpmgObject.BoolToString(s.IsPredefined);
            p.Add("Owner_Staff_No", DbType.String).Value = s.OwnerStaffNo;
            p.Add("Create_Time", DbType.DateTime).Value = s.CreatedTime;
            p.Add("Create_User", DbType.String).Value = s.CreatedUser;

            template.ExecuteNonQuery(CommandType.Text, strSql, p);

        }
        #endregion

        internal static IList<Search> ListSearchs(string staffNo)
        {
            string strSql = strBase + " where owner_staff_no = @no ";
            IDbParameters p = template.CreateDbParameters();
            p.Add("no", DbType.String).Value = staffNo;

            IList<Search> lst = template.QueryWithRowMapperDelegate(CommandType.Text, strSql,
                new RowMapperDelegate<Search>(RowMap), p);
            FillConditions(lst);

            return lst;
        }

        internal static IList<Search> ListAllSearchs()
        {
            string strSql = strBase;
            IList<Search> lst = template.QueryWithRowMapperDelegate(CommandType.Text, strSql, new RowMapperDelegate<Search>(RowMap));
            FillConditions(lst);

            return lst;
        }

        internal static void CreateSearch(Search obj)
        {
            //Get Search_ID
            obj.SearchID = UniqueIDGenerator.Get("S");

            #region Insert into table: search
            string strIns = "insert into search values(@id, @name, @isPredefined, @owner, @cTime, @cUser) ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("id", DbType.String).Value = obj.SearchID;
            p.Add("name", DbType.String).Value = obj.Name;
            p.Add("isPredefined", DbType.String).Value = KpmgObject.BoolToString(obj.IsPredefined);
            p.Add("owner", DbType.String).Value = ChangeDisplayNameToStaffNo(obj.OwnerStaffNo);
            p.Add("cTime", DbType.DateTime).Value = obj.CreatedTime;
            p.Add("cUser", DbType.String).Value = obj.CreatedUser;

            template.ExecuteNonQuery(CommandType.Text, strIns, p);
            #endregion

            #region Insert into table: search_condition
            strIns = "insert into search_condition values(@id, @sId, @key, @oper, @val, @dType) ";

            foreach (SearchCondition sc in obj.Conditions)
            {
                IDbParameters p1 = template.CreateDbParameters();
                p1.Add("id", DbType.String).Value = UniqueIDGenerator.Get("C");
                p1.Add("sId", DbType.String).Value = obj.SearchID;
                p1.Add("key", DbType.String).Value = sc.Key.ToString();
                p1.Add("oper", DbType.String).Value = sc.Operator.ToString();
                p1.Add("val", DbType.String).Value = sc.Value; ;
                p1.Add("dType", DbType.String).Value = sc.DataType.ToString();

                template.ExecuteNonQuery(CommandType.Text, strIns, p1);
            }
            #endregion
        }

        #region private methods
        private static string ChangeDisplayNameToStaffNo(string display_Name)
        {
            int val;
            if (int.TryParse(display_Name, out val))

            {
                return display_Name;
            }
            string strSql = "select Staff_NO from v_staff_info where Display_Name like @displayName";
            string staffNo = "";
            IDbParameters p = template.CreateDbParameters();
            p.Add("displayName", DbType.String).Value = display_Name;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader idr)
            {
                staffNo = idr[0].ToString();

            }, p);
            return staffNo;
        }
        private static Search RowMap(IDataReader dr, int rowNumber)
        {
            Search obj = new Search();

            obj.SearchID = dr.GetString(0);
            obj.Name = dr.GetString(1);
            obj.IsPredefined = dr.GetString(2) == "Y" ? true : false;
            obj.OwnerStaffNo = dr.GetString(3);
            obj.CreatedTime = dr.GetDateTime(4);
            obj.CreatedUser = dr.GetString(5);

            return obj;
        }
        //
        public static void InsertIntoCondition(SearchCondition sc, string s_id)
        {
            string strSqlCon = "insert into Search_Condition values(@C_ID,@S_ID,@Key,@Operator,@Value,@DataType)";
            IDbParameters p1 = template.CreateDbParameters();
            p1.Add("C_ID", DbType.String).Value = UniqueIDGenerator.Get("C");
            Random rn = new Random();
            // p1.Add("C_ID", DbType.String).Value = "1a2" + rn.Next(1, 10000);
            p1.Add("S_ID", DbType.String).Value = s_id;
            p1.Add("Key", DbType.String).Value = sc.Key;
            p1.Add("Operator", DbType.String).Value = sc.Operator;
            p1.Add("Value", DbType.String).Value = sc.Value;
            p1.Add("DataType", DbType.String).Value = sc.DataType;
            template.ExecuteNonQuery(CommandType.Text, strSqlCon, p1);
        }
        private static void FillConditions(IList<Search> lst)
        {
            if (lst == null || lst.Count == 0)
                return;

            IDictionary<string, Search> dic = lst.ToDictionary(x => x.SearchID, y => y);

            string strSql = "select con_id, search_id, con_key, con_operator, con_value, data_type "
                + " from search_condition "
                + " where search_id in (" + DbConvert.ListToInClause(dic.Select(x => x.Key).ToList()) + ") ";

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                string strID = dr.GetString(0);
                string strSearchID = dr.GetString(1);
                string strKey = dr.GetString(2);
                string strOper = dr.GetString(3);
                string strValue = dr.GetString(4);
                string strType = dr.GetString(5);

                SearchKey enumKey = (SearchKey)Enum.Parse(typeof(SearchKey), strKey, true);
                SearchOperator enumOper = (SearchOperator)Enum.Parse(typeof(SearchOperator), strOper, true);
                SearchDataType enumType = (SearchDataType)Enum.Parse(typeof(SearchDataType), strType, true);

                SearchCondition obj = new SearchCondition(enumKey, enumOper, strValue, enumType);

                dic[strSearchID].Conditions.Add(obj);
            });

        }
        #endregion

        internal static void DeleteSearch(string SearchID)
        {
            string strDel = "delete from Search_Condition where Search_ID = @SearchID;";
            strDel += "delete from Search where Search_ID = @SearchID";
            IDbParameters p = template.CreateDbParameters();
            p.Add("SearchID", DbType.String).Value = SearchID;
            template.ExecuteNonQuery(CommandType.Text, strDel, p);
        }
    }

    public static class SearchOM
    {
        public static Search GetSearch(string searchID)
        {
            if (String.IsNullOrEmpty(searchID))
                return null;

            return SearchDAO.GetSearch(searchID);
        }
        /**/
        public static IList<MyTicket> SearchTicket(IList<SearchCondition> lst)
        {
            if (lst == null)
                return new List<MyTicket>();
            return SearchDAO.SearchTicket(lst);
        }

        public static IList<Search> ListSearchs(string staffNo)
        {
            if (String.IsNullOrEmpty(staffNo))
                return new List<Search>();

            return SearchDAO.ListSearchs(staffNo);
        }

        public static IList<Search> ListAllSearchs()
        {
            return SearchDAO.ListAllSearchs();
        }

        public static void CreateSearchConditon(Search s)
        {
            if (s == null)
            {
                throw new Exception("SearchOM.CreateSearch::Invalid parameter");
            }
            s.CreatedTime = DateTime.Now;
            SearchDAO.CreateSearchCondition(s);
            for (int i = 0; i < s.Conditions.Count(); i++)
            {
                SearchDAO.InsertIntoCondition(s.Conditions[i], s.SearchID);
            }
        }
        public static void CreateSearch(Search obj, string oper)
        {
            if (obj == null || String.IsNullOrEmpty(oper))
                throw new Exception("SearchOM.CreateSearch::Invalid parameter");

            //Modify data
            obj.CreatedTime = DateTime.Now;
            obj.CreatedUser = oper;

            //Insert into database
            SearchDAO.CreateSearch(obj);
            LogUtil.LogEvent(obj.SearchID, EventAction.CreateSearch, oper, obj.ToString());

        }

        public static void DeleteSearch(string SearchID)
        {
            if (!String.IsNullOrEmpty(SearchID))
            {
                SearchDAO.DeleteSearch(SearchID);
            }
        }

    }
}
